package com.dao.impl;

import com.dao.ISupplierWhiteListDao;
import com.pojo.SupplierWhiteList;
import com.util.DBHelper;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class SupplierWhiteListDaoImpl implements ISupplierWhiteListDao {
    private DBHelper db = new DBHelper();

    // 1. 添加供应商到白名单
    public int insertWhiteList(SupplierWhiteList whiteList) {
        String sql = "insert into supplierWhiteList values(null,?, ?, ?, ?)";
        Object[] objs = {whiteList.getEnterpriseId(), whiteList.getSupplierId(),
                whiteList.getSupplierLevel(), whiteList.getUpdateDate()};
        return db.dtUpdate(sql, objs);
    }

    // 分页查询企业的白名单列表  查询当前页的数据
    @Override
    public ArrayList<SupplierWhiteList> selectWhiteListByEnterpriseId(Integer enterpriseId,Integer pageIndex) {
        String sql = "select * from supplierWhiteList where enterpriseId=? limit ?,5";
        int index = (pageIndex- 1) * 5;
        return db.dtQuery(sql, SupplierWhiteList.class, enterpriseId, index);
    }

    //查询企业白名单总数
    @Override
    public int selectWhiteListCount(Integer enterpriseId) {
        String sql = "select count(*) from supplierWhiteList where enterpriseId = ?";
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql);
            ps.setInt(1, enterpriseId);
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, resultSet);
        }
        return 0;
    }


    // 删除白名单（移入黑名单时用） 将白名单企业移至黑名单
    @Override
    public int deleteWhiteList(Integer enterpriseId, Integer supplierId) {
        String sql = "delete from supplierWhiteList where enterpriseId = ? and supplierId = ?";
        return db.dtUpdate(sql, enterpriseId, supplierId);
    }

    //   检查企业是否已在白名单中
    @Override
    public boolean checkInWhiteList(Integer enterpriseId, Integer supplierId) {
        String sql = "select * from supplierWhiteList where enterpriseId = ? and supplierId = ?";
        ArrayList<SupplierWhiteList> list = db.dtQuery(sql, SupplierWhiteList.class, enterpriseId, supplierId);
        return !list.isEmpty();
    }

    // 按条件分页查询白名单（企业名称模糊查询、企业评级）
    @Override
    public ArrayList<SupplierWhiteList> selectWhiteListByCondition(Integer enterpriseId, String supplierName, String supplierLevel,Integer pageIndex) {
        StringBuilder sql = new StringBuilder("select swl.* from supplierWhiteList swl " +
                "join enterprise e ON swl.supplierId = e.id " +
                "where swl.enterpriseId = ?");
        ArrayList<Object> params = new ArrayList<>();
        params.add(enterpriseId);

        if (supplierName != null && !supplierName.trim().isEmpty()) {
            sql.append(" AND e.name LIKE ?");
            params.add("%" + supplierName + "%");
        }
        if (supplierLevel != null && !supplierLevel.isEmpty()) {
            sql.append(" AND swl.supplierLevel = ?");
            params.add(supplierLevel);
        }
        sql.append(" LIMIT ?, 5");
        int index = (pageIndex-1)*5;
        params.add(index);
        return db.dtQuery(sql.toString(), SupplierWhiteList.class, params.toArray());

    }

    // 按条件查询白名单总数
    @Override
    public int selectWhiteListCountByCondition(Integer enterpriseId, String supplierName, String supplierLevel) {
        StringBuilder sql = new StringBuilder("select count(*) from supplierWhiteList swl " +
                "join enterprise e ON swl.supplierId = e.id " +
                "where swl.enterpriseId = ?");
        ArrayList<Object> params = new ArrayList<>();
        params.add(enterpriseId);
        
        if (supplierName != null && !supplierName.trim().isEmpty()) {
            sql.append(" AND e.name LIKE ?");
            params.add("%" + supplierName + "%");
        }
        if(supplierLevel != null && !supplierLevel.trim().isEmpty()) {
            sql.append(" AND swl.supplierLevel = ?");
            params.add(supplierLevel);
        }
        
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, resultSet);
        }
        return 0;
    }


}
